package com.simpleauth.base;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.Writer;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Enumeration;
import java.util.List;
import java.util.Map;
import java.util.Vector;

import com.mysql.jdbc.Driver;

import org.apache.log4j.Logger;



public class DatabaseAccess {
	private static final Logger log = Logger.getLogger(DatabaseAccess.class);
	// dburi var and setter/getter
	private String db_uri;
	private void setDbURI(String db_uri) {
		this.db_uri = db_uri;
	}
	private String getDbURI() {
		return this.db_uri;
	}
	// db type
	private String db_type;
	private void setDbType(String db_type) {
		this.db_type = db_type;
	}
	private String getDbType() {
		return this.db_type;
	}
	// db user if we're oracle and it's not in the uri
        private String db_user;
	private void setDbUser(String db_user) {
		this.db_user = db_user;
	}
	private String getDbUser() {
		return this.db_user;
	}
	// db pass for oracle and it's not in the uri
	private String db_pass;
	private void setDbPass(String db_pass) {
		this.db_pass = db_pass;
	}
	private String getDbPass() {
		return this.db_pass;
	}

	public static void main(String[] args) throws Exception {

	} // main()

	SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");
	SqlStatements sql = new SqlStatements();

	public DatabaseAccess() {}
	
	public DatabaseAccess(String db_uri, String db_type) {
		this.setDbURI(db_uri);
		this.setDbType(db_type);
	}
	
	public DatabaseAccess(String db_uri, String db_type, String db_user, String db_pass) {
		this.setDbURI(db_uri);
		this.setDbType(db_type);
		this.setDbUser(db_user);
		this.setDbPass(db_pass);
	}

	
	/**
	 * escapes single quotes & trims 'str';
	 * if 'str' is null, returns empty string
	 * @param str
	 * @return
	 */	
	protected static String checkString(String str) {
		if (str == null) { return ""; } // if (str == null)

		String newString = "";
		int index = 0;
		while (index >= 0) {
			index = str.indexOf("'");

			if (index >= 0) {
				newString += str.substring(0, index + 1) + "'";
				str = str.substring(index + 1);
			}// if(index >=0)

			else {
				newString += str;
			}// else (index <0)
		}// while ()
		return newString.trim();
	}// checkString()
	
	/**
	 * @param str
	 * @return empty string if str is null, else str
	 */
	private String checkNull(String str) {
		if (str == null) {
			return "";
		} // if (str == null)
		else {
			return str;
		}// else
	} // checkNull()


	private synchronized boolean doNormalInsert(Connection con, String query) throws Exception {
		if (query == null) { return false; }
		Statement stmt = null;
		try {
			stmt = con.createStatement();
			stmt.executeUpdate(query);
			stmt.close();
			return true;
		}
		catch (SQLException e) {
			log.warn("doNormalInsert: " + query, e);

			try {
				stmt.close();
			}
			catch (Exception e2) {}
			throw e;
		}
	}// doNormalInsert()

	/**
	 * performs one update statement, not in a transaction
	 * 
	 * @param con
	 * @param query
	 * @return the number of affected rows
	 * @throws Exception
	 */
	private synchronized int doNormalUpdate(Connection con, String query) throws Exception {
		int rowCount = 0;
		Statement stmt = con.createStatement();
		rowCount = stmt.executeUpdate(query);
		stmt.close();
		return rowCount; // if an exception was not throw we return true
	}// doNormalUpdate()

	/**
	 * performs an array of update statements, not in a transaction
	 * 
	 * @param con
	 * @param queries
	 * @return the number of affected rows
	 * @throws Exception
	 */
	private synchronized int doNormalUpdate(Connection con, String queries[]) throws Exception {
		int result = 0;
		Statement stmt = null;

		try {
			stmt = con.createStatement();

			for (int x = 0; x < queries.length; x++) {
				result += stmt.executeUpdate(queries[x]);
			}
		}
		catch (Exception e) {
			log.warn("dba.doNormalInsert,: ", e);
			for (int x = 0; x < queries.length; x++) {
				log.warn("dba.doNormalInsert, queries[" + x + "]: " + queries[x]);
			}
		}
		finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
			}
			catch (Exception e) {}
		}
		return result;
	}

	private ResultSet doSelectQuery(Connection con, String query) {
		ResultSet rs = null;
		Statement stmt = null;
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(query);
		}
		catch (Exception e) {
			log.warn("doSelectQuery: " + query, e);
		} // catch()

		return rs;
	} // doSelectQuery()

    private ResultSet doScrollableSelectQuery(Connection con, String query) {
        ResultSet rs = null;
        Statement stmt = null;
        try {
            stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            rs = stmt.executeQuery(query);
        }
        catch (Exception e) {
            log.warn("doSelectQuery: " + query, e);
        } // catch()

        return rs;
    } // doScrollableSelectQuery()

	private synchronized boolean doTransactUpdate(Connection con, String[] queries) {
		boolean result = true;
		int i = 0;
		// we need to use a transaction so we have to turn off AutoCommit
		Statement stmt = null;
		try {
			con.setAutoCommit(false);
			stmt = con.createStatement();
			for (i = 0; i < queries.length; i++) {
				log.debug("doTransactUpdate: " + queries[i]);
				stmt.execute(queries[i]);
			} // for (queries.length)
			con.commit();
		}
		catch (Exception e) {
			result = false;
			log.warn("doTransactUpdate(): " + queries[i], e);
			try {
				con.rollback();
			}
			catch (Exception e2) {
				log.warn("doTransactUpdate", e);
			} // catch()
		} // catch()
		finally {
			try {
				stmt.close();
				con.setAutoCommit(true);
			}
			catch (Exception e) {}
		}// finally()
		return result;
	} // doTransactUpdate()


	public synchronized Connection getConnection() 
	    throws IOException {
		Connection con = null;
		String driverName = "";
		if (this.getDbType().equalsIgnoreCase("mysql")) {
			driverName = "com.mysql.jdbc.Driver";
		        try {
	        		Class.forName(driverName).newInstance(); 
		        	con = DriverManager.getConnection(this.getDbURI());
		        }
        		catch (Exception e) {
			        log.warn("getConnection()", e);
        		} // catch()
		} else if (this.getDbType().equalsIgnoreCase("oracle")){
			driverName = "oracle.jdbc.driver.OracleDriver";
		        try {
	        		Class.forName(driverName).newInstance(); 
		        	con = DriverManager.getConnection(this.getDbURI(),
			                                          this.getDbUser(),
			        				  this.getDbPass());
		        }
        		catch (Exception e) {
			        log.warn("getConnection()", e);
        		} // catch()
                }
		return con;
	} // getConnection()

	public boolean getAuthenticated(
			Connection con,
			String username,
			String password) throws Exception {
		boolean authenticated = false;
		String query = sql.getAuthenticated(username, password);
		//log.debug("get authentication query is: " + query);
		
		ResultSet result = this.doSelectQuery(con, query);
        if (result.next()) {
        	authenticated = true;
        } else {
        	authenticated = false;
        }
		result.close();
		return authenticated;
	}
	
	public boolean getAuthorized(
			Connection con,
			String servicename,
			String username,
			String rolename) throws Exception {
		boolean authorized = false;
		String svc_query = sql.getServiceRole(servicename, rolename);
		String usr_query = sql.getUserRole(username, rolename);
		
		ResultSet svc_result = this.doSelectQuery(con, svc_query);
        ResultSet usr_result = this.doSelectQuery(con, usr_query);		
        if (svc_result.next() && usr_result.next()) {
        	authorized = true;
        } else {
        	authorized = false;
        }
		svc_result.close();
		usr_result.close();
		return authorized;
	}
	
	public Vector getRolesForUser(
			Connection con,
			String servicename,
			String username) throws Exception {
		Vector roles = new Vector();
		
		String roles_query = sql.getRolesForUser(servicename, username);
		ResultSet roles_result = this.doSelectQuery(con, roles_query);
		while (roles_result.next()) {
			roles.addElement(roles_result.getString(0));
		}
		return roles;
	}

} // class DatabaseAccess
